/*
 * OracleConnector.java
 *
 * Created on November 18, 2007, 3:13 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

/**
 *
 * @author joseacuna-rohter
 */
public class OracleConnector {
    
    private Connection con=null;
    
    /** Creates a new instance of OracleConnector */
    public OracleConnector() {
      
     try{
      Class.forName("oracle.jdbc.driver.OracleDriver");
      con=DriverManager.getConnection(
        "jdbc:oracle:thin:@babbage2.cs.iit.edu:1521:dev101ee",
        "jacunar",
        "jacunar");
     }catch(Exception e){
        e.printStackTrace();
     }  
      
    }
    
    /*
     *Pass in the name of table and column you want to select.
     *NOTE: Column index begins at 1
     */
    public List Select(String table,int col){
        
        List<String> resultList = new ArrayList();
        
        try {

              Statement stmt =con.createStatement();
              ResultSet rs = stmt.executeQuery("SELECT * FROM " + table);
           
              while(rs.next()){               
                 resultList.add(rs.getString(col));                  
              }

              stmt.close();
              
       }
       catch(Exception e){
           e.printStackTrace();
       }
        
       return resultList;   
    }
    
    /*
     *Pass in the name of table and column you want to select.
     *NOTE: Column is column Name in database
     */
    public List Select(String table,String col){
        
        List<String> resultList = new ArrayList();
        
        try {

              Statement stmt =con.createStatement();
              ResultSet rs = stmt.executeQuery("SELECT * FROM " + table);
           
              while(rs.next()){               
                 resultList.add(rs.getString(col));                  
              }

              stmt.close();
              
       }
       catch(Exception e){
           e.printStackTrace();
       }
        
       return resultList;   
    }
    
    
     /*
     *Pass in the name of table and column you want to select.
     *Also pass in conditions(BEWARE OF QUOTES!)
     *NOTE: Column is column Name in database
     */
    public ResultSet SelectRS(String table,String conditions){
        
        List<String> resultList = new ArrayList();
        ResultSet rs = null;
        try {

              Statement stmt =con.createStatement();
               rs = stmt.executeQuery("SELECT * FROM " + table
                      +" WHERE "+conditions);
           
             return rs;

             
              
       }
       catch(Exception e){
           e.printStackTrace();
       }
        
       return rs;   
    }
    
    
     /*
     *Pass in the name of table and column you want to select.
     *Also pass in conditions(BEWARE OF QUOTES!)
     *NOTE: Column is column Name in database
     */
    public ResultSet SelectAdvRS(String query){
        
        
        ResultSet rs = null;
        try {
              System.out.println(query);
              Statement stmt =con.createStatement();
              rs = stmt.executeQuery(query);           
             return rs;         
       }
       catch(Exception e){
           e.printStackTrace();
       }   
       return rs;   
    }
    
    
    
    
     /*
     *Pass in the name of table and column you want to select.
     *Also pass in conditions(BEWARE OF QUOTES!)
     *NOTE: Column is column Name in database
     */
    public ResultSet SelectRS(String table){
        
        List<String> resultList = new ArrayList();
        ResultSet rs = null;
        try {

              Statement stmt =con.createStatement();
               rs = stmt.executeQuery("SELECT * FROM " + table);
             return rs;

             
              
       }
       catch(Exception e){
           e.printStackTrace();
       }
        
       return rs;   
    }
    
    
     /*
     *Pass in the name of table and a list of conditions
     *Also pass in conditions(BEWARE OF QUOTES!)
     *NOTE: Column is column Name in database
     */
    public ResultSet SelectRS(String table,HashMap<String,String> conditions){
        
        
        ResultSet rs = null;
        String queryString = "SELECT * FROM " + table;
        String tmp;
        boolean firstTime = true;
        try {

            
            Iterator condIt = conditions.keySet().iterator();
            while(condIt.hasNext()){
                tmp = condIt.next().toString();
                if(firstTime && !conditions.get(tmp).equals("")){
                   
                    if(tmp.equals("ADDRESS"))
                        queryString = queryString + " WHERE " + tmp + " LIKE '%" + conditions.get(tmp) +"%'";
                    else
                        queryString = queryString + " WHERE " + tmp + " = '" + conditions.get(tmp) +"'";
                    
                    firstTime = false;
                }
                else if(tmp.equals("ADDRESS") && !conditions.get(tmp).equals("")){
                    queryString = queryString + " AND " + tmp + " LIKE '%" + conditions.get(tmp) +"%'";
                }
                else if(!conditions.get(tmp).equals("")){
                    queryString = queryString + " AND " + tmp + " = '" + conditions.get(tmp) +"'";
                }
                
            }//End of while loop
            
             Statement stmt =con.createStatement();
             System.out.println(queryString);
             rs = stmt.executeQuery(queryString);
             return rs;

             
              
       }
       catch(Exception e){
           e.printStackTrace();
       }
        
       return rs;   
    }
    
    
     /*
      *RETURNS A RESULT SET
     *Pass in the name of table and column you want to select.
     *Also pass in conditions(BEWARE OF QUOTES!)
     *NOTE: Column is column Name in database
     */
    public List Select(String table,String col,String conditions){
        
        List<String> resultList = new ArrayList();
        
        try {

              Statement stmt =con.createStatement();
              ResultSet rs = stmt.executeQuery("SELECT * FROM " + table
                      +" WHERE "+conditions);
           
              while(rs.next()){               
                 resultList.add(rs.getString(col));                  
              }

              stmt.close();
              
       }
       catch(Exception e){
           e.printStackTrace();
       }
        
       return resultList;   
    }
    
    
    
     /*
     *Pass In a SQL statement(I.E. insert, or create Table
     *NOTE: Beware of quotes, use \ for escape character as needed
     */
    public void Execute(String statement){
       
        try {
              Statement stmt =con.createStatement();
              System.out.println("here" + statement);
              stmt.execute(statement);
              stmt.close();
              
       }
       catch(Exception e){
           e.printStackTrace();
       }     
    }
    
    
    /*
    *Pass in table Name, and values to insert into that table
    *NOTE: Beware of quotes, use \ for escape character as needed
    */
    public void ExecuteInsert(String table,String values){
       
        try {
              Statement stmt =con.createStatement();
              stmt.execute("INSERT INTO " + table +
                        " VALUES("+values+")");
              stmt.close();
              
       }
       catch(Exception e){
           e.printStackTrace();
       }     
    }
    
    /*
    *@Pass in generic update statement
    *NOTE: Beware of quotes, use \ for escape character as needed
    */
    public void ExecuteUpdate(String statement){
       
        try {
              Statement stmt =con.createStatement();
              stmt.executeUpdate(statement);
              stmt.close();
              
       }
       catch(Exception e){
           e.printStackTrace();
       }     
    }
    
    /*
    *@Pass in generic update statement
    *NOTE: Beware of quotes, use \ for escape character as needed
    */
    public void ExecuteUpdate(String table,String col,String value,
                    String conditions){
       
        try {
              Statement stmt =con.createStatement();
              stmt.executeUpdate("UPDATE "+table+" set "+col+" = "
                      + value +" WHERE "+conditions);
              stmt.close();
              
       }
       catch(Exception e){
           e.printStackTrace();
       }     
    }
    
    
    
    
    public void CloseCon(){
         try{
              con.close();
              System.out.println("**CONNECTION CLOSED**");
         }catch(Exception e){
             e.printStackTrace(); 
         }
    }
    
}
